package com.yeskery.transfer.shell;

import com.yeskery.transfer.config.DataBaseProperties;
import com.yeskery.transfer.core.KeyList;
import com.yeskery.transfer.core.Resource;
import com.yeskery.transfer.core.Result;
import com.yeskery.transfer.core.SproutException;
import com.yeskery.transfer.data.ConnectionKit;
import com.yeskery.transfer.data.DataKit;
import com.yeskery.transfer.data.MetaData;
import org.springframework.shell.Availability;
import org.springframework.shell.standard.ShellComponent;
import org.springframework.shell.standard.ShellMethod;
import org.springframework.shell.standard.ShellMethodAvailability;
import org.springframework.shell.standard.ShellOption;

import javax.validation.constraints.NotBlank;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 表结构转移的shell
 * @author shuangjiang.du
 * @version 1.0
 * @date 2018-12-18 22:21
 */
@ShellComponent
public class TableTransferShell {

    /** 连接工具 */
    private final ConnectionKit connectionKit;

    /** 数据工具 */
    private final DataKit dataKit;

    /** 系统资源对象 */
    private final Resource resource;

    public TableTransferShell(ConnectionKit connectionKit, DataKit dataKit, Resource resource) {
        this.connectionKit = connectionKit;
        this.dataKit = dataKit;
        this.resource = resource;
    }

    /**
     * 查询服务器端的所有数据表
     * @return 服务器端的所有数据表
     */
    @ShellMethod(value = "Query all data tables on the server.", key = {"tab-serv-ls", "table-server-ls"})
    public Result getServerTables() {
        return createTableNamesResult(true, resource.get(KeyList.DATA_CONFIG_KEY,
                DataBaseProperties.class).getServerTransferProperties());
    }

    /**
     * 查询客户端的所有数据表
     * @return 客户端的所有数据表
     */
    @ShellMethod(value = "Query all data tables on the client.", key = {"tab-cli-ls", "table-client-ls"})
    public Result getClientTables() {
        return createTableNamesResult(false, resource.get(KeyList.DATA_CONFIG_KEY,
                DataBaseProperties.class).getClientTransferProperties());
    }

    /**
     * 查询服务器端的指定表的字段
     * @param tableName 表名
     * @return 服务器端的指定表的字段
     */
    @ShellMethod(value = "Query the fields of the specified table on the server."
            , key = {"tab-serv-fls", "table-server-field-ls"})
    public Result getServerTableFields(@NotBlank String tableName) {
        return createTableFieldsResult(true, resource.get(KeyList.DATA_CONFIG_KEY,
                DataBaseProperties.class).getServerTransferProperties(), tableName);
    }

    /**
     * 查询客户端的指定表的字段
     * @param tableName 表名
     * @return 客户端的指定表的字段
     */
    @ShellMethod(value = "Query the fields of the specified table on the client."
            , key = {"tab-cli-fls", "table-client-field-ls"})
    public Result getClientTableFields(@NotBlank String tableName) {
        return createTableFieldsResult(false, resource.get(KeyList.DATA_CONFIG_KEY,
                DataBaseProperties.class).getClientTransferProperties(), tableName);
    }

    /**
     * 查询服务器端指定的表是否存在
     * @param tableName 表名
     * @return <code>true</code> for exist, else not.
     */
    @ShellMethod(value = "Query whether the table specified on the server exists."
            , key = {"tab-serv-ex", "table-server-exist"})
    public Result getServerTableExist(@NotBlank String tableName) {
        DataBaseProperties.TransferProperties transferProperties =
                resource.get(KeyList.DATA_CONFIG_KEY, DataBaseProperties.class).getServerTransferProperties();
        return createExistTableResult(transferProperties, tableName, true);
    }

    /**
     * 查询客户端指定的表是否存在
     * @param tableName 表名
     * @return <code>true</code> for exist, else not.
     */
    @ShellMethod(value = "Query whether the table specified on the client exists."
            , key = {"tab-cli-ex", "table-client-exist"})
    public Result getClientTableExist(String tableName) {
        DataBaseProperties.TransferProperties transferProperties =
                resource.get(KeyList.DATA_CONFIG_KEY, DataBaseProperties.class).getClientTransferProperties();
        return createExistTableResult(transferProperties, tableName, false);
    }

    /**
     * 在客户端创建服务器的指定表
     * @param tablePattern 需要创建表的正则表达式，默认为全部表
     * @param force <code>Boolean</code>值，默认为<code>false</code>。是否强制传输，如果该值为<code>true</code>，
     *              如果客户端也存在这些表，将会先删除客户端的这些表，再重新创建表。
     */
    @ShellMethod(value = "Create a specified table for the server on the client."
            , key = {"tab-tran", "table-transfer"})
    public void createTable(@ShellOption(value = {"--table", "--t"}, defaultValue = ".*") String tablePattern,
                            @ShellOption(value = {"--force", "--f"}, defaultValue = "false") String force) {
        DataBaseProperties dataBaseProperties = resource.get(KeyList.DATA_CONFIG_KEY, DataBaseProperties.class);
        try (Connection serverConnection = connectionKit.getConnection(dataBaseProperties.getServerTransferProperties());
             Connection clientConnection = connectionKit.getConnection(dataBaseProperties.getClientTransferProperties())){
            List<String> tableNameList = getPatternTableList(serverConnection, tablePattern);
            if (tableNameList == null || tableNameList.size() < 1) {
                System.out.println("Not matched table[" + tablePattern + "]");
                System.out.println(new Result().createBlankLine());
                return;
            }
            System.out.println("Start copying from " + dataBaseProperties.getServerTransferProperties().getHost() + " to "
                    + dataBaseProperties.getClientTransferProperties().getHost());
            for (String tableName : tableNameList) {
                System.out.println("Start creating tables: " + tableName + " ...");
                List<MetaData> tableMetaDataList = dataKit.getMetaData(serverConnection, tableName);
                if (Boolean.parseBoolean(force)) {
                    dataKit.deleteTable(clientConnection, tableName);
                }
                dataKit.createTable(clientConnection, tableName, tableMetaDataList);
                System.out.println("Table[" + tableName + "] create success");
            }
            System.out.println(new Result().createBlankLine());
        } catch (SQLException | ClassNotFoundException e) {
            throw new SproutException("Data table does not exist or database connection fails.", e);
        }
    }

    /**
     * 检查服务器配置有效性
     * @return Availability对象
     */
    @ShellMethodAvailability({"tab-serv-ls", "table-server-ls", "tab-serv-fls", "table-server-field-ls"
            , "tab-serv-ex", "table-server-exist"})
    public Availability serverCommandAvailability() {
        return resource.get(KeyList.SERVER_CONFIG_VALID, false) ? Availability.available() :
                Availability.unavailable("Set up the server database configuration information first.");
    }

    /**
     * 检查客户端配置有效性
     * @return Availability对象
     */
    @ShellMethodAvailability({"tab-cli-ls", "table-client-ls", "tab-cli-fls", "table-client-field-ls"
            , "tab-cli-ex", "table-client-exist"})
    public Availability clientCommandAvailability() {
        return resource.get(KeyList.CLIENT_CONFIG_VALID, false) ? Availability.available() :
                Availability.unavailable("Set up the client database configuration information first.");
    }

    /**
     * 检查客户端和管理端的配置信息有效性
     * @return Availability对象
     */
    @ShellMethodAvailability({"tab-tran", "table-transfer"})
    public Availability databaseCommandAvailability() {
        return resource.get(KeyList.CLIENT_CONFIG_VALID, false)
                && resource.get(KeyList.SERVER_CONFIG_VALID, false) ? Availability.available() :
                Availability.unavailable("Set up the database configuration information first.");
    }

    /**
     * 创建一个数据库表列表的结果对象
     * @param isServer <code>true</code> for server,else not.
     * @param transferProperties 数据库配置信息
     * @return 数据库表列表的结果对象
     */
    private Result createTableNamesResult(boolean isServer, DataBaseProperties.TransferProperties transferProperties) {
        try (Connection connection = connectionKit.getConnection(transferProperties)){
            List<String> tableList = dataKit.getTableNames(connection);
            String content = tableList.stream().reduce("Table Name", (t, k) -> t + "\n" + k);
            Result result = new Result();
            result.setTitle(isServer ? "Server database table" : "Client database table");
            result.setContent(content);
            return result;
        } catch (SQLException | ClassNotFoundException e) {
            throw new SproutException("Database connection creation failed.", e);
        }
    }

    /**
     * 从指定的数据库连接获取符合指定的正则表达式的表列表
     * @param connection 数据库连接
     * @param tablePattern 表的正则表达式
     * @return 匹配正则表达式的表列表
     * @throws SQLException SQLException
     */
    private List<String> getPatternTableList(Connection connection, String tablePattern) throws SQLException {
        List<String> tableNameList = dataKit.getTableNames(connection);
        Pattern pattern = Pattern.compile(tablePattern);
        return  tableNameList.stream().filter(
                tableName -> pattern.matcher(tableName).matches()).collect(Collectors.toList());
    }

    /**
     * 创建表是否存在的结果
     * @param transferProperties 数据库配置对象
     * @param tableName 表名
     * @param isServer 是否是服务器端配置
     * @return 表是否存在的结果
     */
    private Result createExistTableResult(DataBaseProperties.TransferProperties transferProperties, String tableName
            , boolean isServer) {
        try (Connection connection = connectionKit.getConnection(transferProperties)) {
            boolean exist = dataKit.existTable(connection, tableName);
            Result result = new Result();
            result.setTitle("Query " + (isServer ? "server" : "Client") + " table exist");
            result.setContent(result.createContentLine("Table[" + tableName + "] " + (exist ? "exist" : "non-existent")));
            return result;
        } catch (SQLException | ClassNotFoundException e) {
            throw new SproutException("Database connection creation failed.", e);
        }
    }

    /**
     * 创建一个数据库表字段列表的结果对象
     * @param isServer <code>true</code> for server,else not.
     * @param transferProperties 数据库配置信息
     * @return 数据库表字段列表的结果对象
     */
    private Result createTableFieldsResult(boolean isServer, DataBaseProperties.TransferProperties transferProperties
            , String tableName) {
        try(Connection connection = connectionKit.getConnection(transferProperties)) {
            List<MetaData> metaDataList = dataKit.getMetaData(connection, tableName);
            Result result = new Result();
            String content = metaDataList.stream()
                    .map(metaData ->
                            metaData.getName() + " " + metaData.getType() + " "
                                    + (metaData.getNullable() ? "Yes" : "No") + " "
                                    + metaData.getKey().getValue() + " "
                                    + metaData.getComment())
                    .reduce("Field Type Nullable Constraint Comment", (t, k) -> t + "\n" + k);
            result.setTitle((isServer ? "Server database table" : "Client database table") + "-" + tableName);
            result.setContent(content);
            return result;
        } catch (SQLException | ClassNotFoundException e) {
            throw new SproutException("Data table does not exist or database connection fails.", e);
        }
    }
}
